package com.vepick.metier.requetes;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import com.vepick.beans.Routines;
import com.vepick.metier.lancement.InterfaceSuperviseur;

public class Requetes_routines {

	/*
	 * Lister toutes les routines
	 */

	public static void afficherLesRoutines(Connection conn) throws SQLException {

		List<Routines> lesRoutines = new ArrayList<>();
		String requete = "SELECT idRoutines, immatVehicule, dateRoutines FROM ROUTINES ORDER BY dateRoutines ASC ";
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(requete);
		while (res.next()) {
			lesRoutines.add(new Routines(res.getInt("idRoutines"), res
					.getInt("immatVehicule"), res.getString("dateRoutines")));
		}
		res.close();
		for (int j = 0; j < lesRoutines.size(); j++) {
			System.out.println("Routine n� "
					+ lesRoutines.get(j).getIdRoutines()
					+ ", vehicule associe "
					+ lesRoutines.get(j).getImmatVehicule() + ""
					+ ": date de routines "
					+ lesRoutines.get(j).getDateRoutines());
		}
		System.out.println("\n");
	}

	/*
	 * Lister toutes les routines
	 */
	public static void afficherLesRoutinesParVehicule(Connection conn,
			int immatVehicRoutine) throws SQLException {

		List<Routines> lesRoutines = new ArrayList<>();
		String requete = "SELECT idRoutines, immatVehicule, dateRoutines FROM ROUTINES where immatVehicule="
				+ immatVehicRoutine + " ORDER BY dateRoutines ASC ";
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(requete);
		while (res.next()) {
			lesRoutines.add(new Routines(res.getInt("idRoutines"), res
					.getInt("immatVehicule"), res.getString("dateRoutines")));
		}
		res.close();
		for (int j = 0; j < lesRoutines.size(); j++) {
			System.out.println("Routine n� "
					+ lesRoutines.get(j).getIdRoutines()
					+ ", vehicule associe "
					+ lesRoutines.get(j).getImmatVehicule() + ""
					+ ": date de routines "
					+ lesRoutines.get(j).getDateRoutines());
		}
		System.out.println("\n");
	}

	/*
	 * Creer une nouvelle routine
	 */

	public static void enregistrerRoutine(Connection conn, int immatVehicule,
			String dateRoutines) {
		try {
			String requeteAjoutRoutine = "INSERT INTO ROUTINES VALUES(idRoutine_seq.nextVal,"
					+ immatVehicule
					+ ",TO_DATE('"
					+ dateRoutines
					+ "', 'dd-mm-yyyy hh24:mi:ss'))";
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(requeteAjoutRoutine);

			conn.commit();
		} catch (SQLException e) {
			System.out.println(e);
			System.out.println("Erreur a la creation de routine !\n");
		}
	}

	public static void modifierRoutines(Connection conn, int numRout,
			int immatVehic, String dateRout) {
		try {
			String requeteModifierRoutines = "UPDATE ROUTINES SET IMMATVEHICULE=" + immatVehic
					+ ",DATEROUTINES=" + "TO_DATE('" + dateRout
					+ "', 'dd-mm-yyyy hh24:mi:ss')WHERE IDROUTINES="+numRout;
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(requeteModifierRoutines);

			conn.commit();
		} catch (SQLException e) {
			System.out.println(e);
			System.out.println("Erreur a la modification de la routine !\n");
		}
		// TODO Auto-generated method stub

	}

	public static int consulterRoutineDuJour(Connection conn, int immatVehicule) {
		String requete = "SELECT idRoutines AS id FROM Routines WHERE IMMATVEHICULE="
				+ immatVehicule + " AND TRUNC(dateRoutines) = TRUNC(Sysdate)";
		Statement stmt;
		int idRoutine = -1;
		try {
			stmt = conn.createStatement();
			ResultSet res = stmt.executeQuery(requete);
			if (res.next()) {
				idRoutine = res.getInt("id");
				res.close();
				return idRoutine;
			} else {
				res.close();
				return -1;
			}
		} catch (SQLException e) {
			System.out.println("Erreur dans la requete consulterRoutineDuJour");
			return -1;
		}
	}

	public static void afficherDetailRoutine(Connection conn, int idRoutines)
			throws SQLException {

		String requete = "SELECT * FROM TACHE WHERE idRoutines =" + idRoutines
				+ " ORDER BY seqtache ASC ";
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(requete);
		// TODO Reorganiser l'info contenue dans le while
		while (res.next()) {
			System.out.println("---------------------------------------------------------------------");
			System.out
					.println("Numero de Station : " + res.getInt("idStation"));
			System.out.println("Numero de Sequence : "
					+ res.getDouble("seqTache"));
			System.out.println("Type de la tache : "
					+ res.getInt("idtypetache"));
			System.out
					.println("Tache annulee ? : " + res.getInt("tacheannule"));
			// System.out.println("Nombre de v�lo : " +
			// res.getInt("nombrevelo"));
		}
		res.close();
		stmt.close();
	}
}
